package com.neuedu.jdbc.dao;

import com.neuedu.jdbc.po.User;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/***
 * 项目名称:  MyJDBC 
 * 包：  com.neuedu.jdbc.dao 
 * date:   2025/11/7   11:30  
 * 描述:  
 *  @author : 张金山
 *  作者博客:   https://blog.fulfill.com.cn
 */
public class UserDaoBak2 {

    /**
     * 根据主键查询单条结果
     *
     * @param id
     * @return
     */
    public User selectById(long id) {


        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "root";


        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        User user = null;

        // 旧版  5.xx-
        // Class.forName("com.mysql.jdbc.Driver");
        // 8.x +
        try {
            // 1 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");


            // 2 创建连接
            conn = DriverManager.getConnection(url, username, password);

            // 3 创建语句块stmt
            stmt = conn.createStatement();

            // 4 执行sql 语句
            String sql = "select  `id`,  `username`, `password`,  `nickname`,  `last_login`, " +
                    " `status`,  `create_time`, `create_user`,  `update_time`, `update_user`   from user where id = " + id;

            rs = stmt.executeQuery(sql);


            // 解析结果集
            if (rs.next()) {

                user = new User(
                        rs.getLong("id"),
                        rs.getString("username"),
                        rs.getString("password"),
                        rs.getString("nickname"),
                        rs.getTimestamp("last_login"),
                        rs.getString("status"),
                        rs.getTimestamp("create_time"),
                        rs.getLong("create_user"),
                        rs.getTimestamp("update_time"),
                        rs.getLong("update_user")


                );

            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }


            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }


        return user;
    }


    /**
     * 根据条件查询集合
     *
     * @param
     * @return
     */
    public List<User> selectList(User conUser) {


        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String username = "root";
        String password = "root";


        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;

        List list = new ArrayList();

        // 旧版  5.xx-
        // Class.forName("com.mysql.jdbc.Driver");
        // 8.x +
        try {
            // 1 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");


            // 2 创建连接
            conn = DriverManager.getConnection(url, username, password);

            // 3 创建语句块stmt
            stmt = conn.createStatement();

            // 4 执行sql 语句
            StringBuffer sql = new StringBuffer("select  `id`,  `username`, `password`,  `nickname`,  `last_login`, " +
                    " `status`,  `create_time`, `create_user`,  `update_time`, `update_user`   from user ");


            if (conUser.getUsername() != null && !"".equals(conUser.getUsername())) {
                sql.append(" where `username` like '%" + conUser.getUsername() + "%'");
            }

            if (conUser.getPassword() != null && !"".equals(conUser.getPassword())) {
                if (sql.toString().contains("where ")) {
                    sql.append(" and `password` like '%" + conUser.getPassword() + "%'");

                } else {
                    sql.append(" where `password` like '%" + conUser.getPassword() + "%'");
                }
            }
            if (conUser.getNickname() != null && !"".equals(conUser.getNickname())) {
                if (sql.toString().contains("where ")) {
                    sql.append(" and `nickname` like '%" + conUser.getNickname() + "%'");

                } else {
                    sql.append(" where `nickname` like '%" + conUser.getNickname() + "%'");
                }
            }


            System.out.println("SQL: " + sql.toString());
            rs = stmt.executeQuery(sql.toString());


            // 解析结果集
            while (rs.next()) {

                User user = new User(
                        rs.getLong("id"),
                        rs.getString("username"),
                        rs.getString("password"),
                        rs.getString("nickname"),
                        rs.getTimestamp("last_login"),
                        rs.getString("status"),
                        rs.getTimestamp("create_time"),
                        rs.getLong("create_user"),
                        rs.getTimestamp("update_time"),
                        rs.getLong("update_user")


                );
                list.add(user);


            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭资源

            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }


            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    throw new RuntimeException(e);
                }
            }

        }


        return list;
    }


    public int updateById(User po) {
        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "root";
        String driverClass = "com.mysql.cj.jdbc.Driver";

        Connection connection = null;
        Statement stmt = null;
        int count = 0;
        try {
            Class.forName(driverClass);

            connection = DriverManager.getConnection(url, user, password);

            stmt = connection.createStatement();

            System.out.println("po.getLastLogin() = " + po.getLastLogin());
            String lastLogin = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(po.getLastLogin());
            String updateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(po.getUpdateTime());
            StringBuilder sql = new StringBuilder();
            sql.append("  update 					");
            sql.append("    `his`.`user` 				");
            sql.append("  set			");
            sql.append("    `username` = '" + po.getUsername() + "',		");
            sql.append("    `password` = '" + po.getPassword() + "',		");
            sql.append("    `nickname` = '" + po.getNickname() + "',		");
            sql.append("    `last_login` = '" + lastLogin + "',		");
            sql.append("    `status` = '" + po.getStatus() + "',		");
            sql.append("    `update_time` = '" + updateTime + "',		");
            sql.append("    `update_user` = '" + po.getUpdateuser() + "'	");

            sql.append("   WHERE `id` = '" + po.getId() + "' 		");


            count = stmt.executeUpdate(sql.toString());


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return count;

    }

    public int insert(User po) {
        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "root";
        String driverClass = "com.mysql.cj.jdbc.Driver";

        Connection connection = null;
        Statement stmt = null;
        int count = 0;
        try {
            Class.forName(driverClass);

            connection = DriverManager.getConnection(url, user, password);

            stmt = connection.createStatement();

            String sql = "   INSERT INTO `his`.`user` ( `username`, `password`,  `nickname` ) VALUES  ( '" + po.getUsername() + "', '" + po.getPassword() + "', '" + po.getNickname() + "' ) ";

            count = stmt.executeUpdate(sql);


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return count;

    }

    public int deleteById(long id) {
        // String url = "http://www.baidu.com";
        String url = "jdbc:mysql://localhost:3306/his?useUnicode=true&characterEncoding=utf8";
        String user = "root";
        String password = "root";
        String driverClass = "com.mysql.cj.jdbc.Driver";

        Connection connection = null;
        Statement stmt = null;
        int count = 0;
        try {
            // 加载驱动
            Class.forName(driverClass);

            // 建立连接
            connection = DriverManager.getConnection(url, user, password);

            // 创建sttm
            stmt = connection.createStatement();

            //  执行SQL语句
            String sql = "delete from user where id = " + id;
            count = stmt.executeUpdate(sql);


        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

            if (connection != null) {
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }

        return count;

    }


    public static void main(String[] args) {

        UserDaoBak2 userDao = new UserDaoBak2();


        // 根据主键查询一条
        // long id = 2;
        // User user = userDao.selectById(id);
        // System.out.println(user);


        // // 根据条件查询多条
        // User conditionUser = new User();
        // conditionUser.setUsername("a");
        // conditionUser.setPassword("123456");
        // conditionUser.setNickname("管理员");
        //
        // List list = userDao.selectList(conditionUser);
        // list.forEach(System.out::println);


        // 插入
        // User user = new User();
        // user.setUsername("abd");
        // user.setPassword("123456");
        // user.setNickname("JDBC");
        // user.setCreateuser(1L);
        // int count = userDao.insert(user);
        // System.out.println("插入条数count = " + count);


        // 根据主键更新
        // long id = 3;
        // User user = userDao.selectById(id);
        // System.out.println(user);
        //
        // user.setLastLogin(new Date());
        // user.setUpdateTime(new Date());
        // user.setNickname("测试昵称");
        // int count = userDao.updateById(user);
        // System.out.println("count = " + count);

        // 根据主键删除一条记录
        long id = 7;
        int count  = userDao.deleteById(id);
        System.out.println("count = " + count);

    }
}
